﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using MSSQLProvider.DataBase;
using ConnectLib.DAO;
using System.Collections;

namespace ConnectLib.DAL
{
    public class StaffDal : MSSQLDataAccess
    {
        private static StaffDal _instance = new StaffDal();

        public static StaffDal Instance
        {
            get
            {
                return _instance;
            }
        }

        public StaffDal()
        {
        }

        private bool IsAccountDuplicate(string staffcode)
        {
            if (ExecuteScalar(string.Format("SELECT StaffCode FROM [Staff] WHERE StaffCode = '{0}'", staffcode)) != null)
            {
                return true;
            }

            return false;
        }

        public int AddStaff(StaffDao staff)
        {
            try
            {
                if (IsAccountDuplicate(staff.StaffCode))
                {
                    return 0;
                }
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("StaffCode");
                arrParamName.Add("@StaffCode");
                arrValue.Add(staff.StaffCode);

                arrFielName.Add("FullName");
                arrParamName.Add("@FullName");
                arrValue.Add(staff.FullName);

                arrFielName.Add("Address");
                arrParamName.Add("@Address");
                arrValue.Add(staff.Address);

                arrFielName.Add("Phone");
                arrParamName.Add("@Phone");
                arrValue.Add(staff.Phone);

                arrFielName.Add("Password");
                arrParamName.Add("@Password");
                arrValue.Add(staff.Password);

                arrFielName.Add("Birthday");
                arrParamName.Add("@Birthday");
                arrValue.Add(staff.Birthday);
                
                arrFielName.Add("PTNRight");
                arrParamName.Add("@PTNRight");
                arrValue.Add(staff.PTNRight);

                arrFielName.Add("Active");
                arrParamName.Add("@Active");
                arrValue.Add(staff.Active);

                arrFielName.Add("Branch");
                arrParamName.Add("@Branch");
                arrValue.Add(staff.Branch);

                arrFielName.Add("Skype");
                arrParamName.Add("@Skype");
                arrValue.Add(staff.Skype);

                arrFielName.Add("Email");
                arrParamName.Add("@Email");
                arrValue.Add(staff.Email);

                arrFielName.Add("Support");
                arrParamName.Add("@Support");
                arrValue.Add(staff.Support);

                arrFielName.Add("Sex");
                arrParamName.Add("@Sex");
                arrValue.Add(staff.Sex);

                var queryBuider = new StringBuilder();
                queryBuider.Append("INSERT INTO [Staff] (");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    queryBuider.Append(arrFielName[i]);
                    if (i < arrFielName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                queryBuider.Append(" VALUES (");
                i = 0;

                for (; i < arrParamName.Count; i++)
                {
                    queryBuider.Append(arrParamName[i]);
                    if (i < arrParamName.Count - 1)
                        queryBuider.Append(",");
                }
                queryBuider.Append(")");
                
                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateStaff(StaffDao staff)
        {
            try
            {
                if (IsAccountDuplicate(staff.StaffCode))
                {
                    return 0;
                }

                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("FullName");
                arrParamName.Add("@FullName");
                arrValue.Add(staff.FullName);

                arrFielName.Add("Address");
                arrParamName.Add("@Address");
                arrValue.Add(staff.Address);

                arrFielName.Add("Phone");
                arrParamName.Add("@Phone");
                arrValue.Add(staff.Phone);

                arrFielName.Add("Password");
                arrParamName.Add("@Password");
                arrValue.Add(staff.Password);

                arrFielName.Add("Birthday");
                arrParamName.Add("@Birthday");
                arrValue.Add(staff.Birthday);

                arrFielName.Add("PTNRight");
                arrParamName.Add("@PTNRight");
                arrValue.Add(staff.PTNRight);

                arrFielName.Add("Active");
                arrParamName.Add("@Active");
                arrValue.Add(staff.Active);

                arrFielName.Add("Branch");
                arrParamName.Add("@Branch");
                arrValue.Add(staff.Branch);

                arrFielName.Add("Skype");
                arrParamName.Add("@Skype");
                arrValue.Add(staff.Skype);

                arrFielName.Add("Email");
                arrParamName.Add("@Email");
                arrValue.Add(staff.Email);

                arrFielName.Add("Support");
                arrParamName.Add("@Support");
                arrValue.Add(staff.Support);

                arrFielName.Add("Sex");
                arrParamName.Add("@Sex");
                arrValue.Add(staff.Sex);

                arrFielName.Add("StaffCode");
                arrParamName.Add("@StaffCode");
                arrValue.Add(staff.StaffCode);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Staff] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateStaffChangeCode(StaffDao staff, string oldstaffcode)
        {
            try
            {
                List<string> arrFielName = new List<string>();
                List<string> arrParamName = new List<string>();
                List<object> arrValue = new List<object>();

                arrFielName.Add("StaffCode");
                arrParamName.Add("@StaffCode");
                arrValue.Add(staff.StaffCode);

                arrFielName.Add("FullName");
                arrParamName.Add("@FullName");
                arrValue.Add(staff.FullName);

                arrFielName.Add("Address");
                arrParamName.Add("@Address");
                arrValue.Add(staff.Address);

                arrFielName.Add("Phone");
                arrParamName.Add("@Phone");
                arrValue.Add(staff.Phone);

                arrFielName.Add("Password");
                arrParamName.Add("@Password");
                arrValue.Add(staff.Password);

                arrFielName.Add("Birthday");
                arrParamName.Add("@Birthday");
                arrValue.Add(staff.Birthday);

                arrFielName.Add("PTNRight");
                arrParamName.Add("@PTNRight");
                arrValue.Add(staff.PTNRight);

                arrFielName.Add("Active");
                arrParamName.Add("@Active");
                arrValue.Add(staff.Active);

                arrFielName.Add("Branch");
                arrParamName.Add("@Branch");
                arrValue.Add(staff.Branch);

                arrFielName.Add("Skype");
                arrParamName.Add("@Skype");
                arrValue.Add(staff.Skype);

                arrFielName.Add("Email");
                arrParamName.Add("@Email");
                arrValue.Add(staff.Email);

                arrFielName.Add("Support");
                arrParamName.Add("@Support");
                arrValue.Add(staff.Support);

                arrFielName.Add("Sex");
                arrParamName.Add("@Sex");
                arrValue.Add(staff.Sex);

                arrFielName.Add("StaffCode");
                arrParamName.Add("@oldstaffcode");
                arrValue.Add(oldstaffcode);

                var queryBuider = new StringBuilder();
                queryBuider.Append("UPDATE [Staff] SET ");
                int i = 0;

                for (; i < arrFielName.Count; i++)
                {
                    if (i != arrFielName.Count - 1)
                    {
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                        if (i < arrFielName.Count - 2)
                            queryBuider.Append(",");
                    }
                    else
                    {
                        queryBuider.Append(" WHERE ");
                        queryBuider.Append(arrFielName[i]);
                        queryBuider.Append("=");
                        queryBuider.Append(arrParamName[i]);
                    }
                }

                return ExecuteNonQuery(queryBuider.ToString(), arrParamName, arrValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool StaffChangeBranch(int oldbranch, int newbranch)
        {
            try
            {
                return ExecuteNonQuery(string.Format("UPDATE [Staff] SET [Branch] = {0} WHERE [Branch] = {1}", newbranch,
                    oldbranch));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private bool CheckPassword(string staffcode, string password)
        {
            if (
                ExecuteScalar(string.Format("SELECT Id FROM [Staff] WITH (NOLOCK) WHERE StaffCode = '{0}' AND Password = '{1}'", staffcode,
                    password)) != null)
            {
                return true;
            }

            return false;
        }

        public bool ChangePassword(string staffcode, string oldpassword, string password)
        {
            try
            {
                if (!CheckPassword(staffcode, oldpassword))
                {
                    return false;
                }

                return
                    ExecuteNonQuery(string.Format("UPDATE [Staff] SET Password = '{0}' where StaffCode = '{1}'", staffcode,
                        password));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool DeleteStaff(string staffcode)
        {
            return ExecuteNonQuery(string.Format("DELETE FROM [Staff] WHERE StaffCode = '{0}'", staffcode));
        }

        public bool DeleteStaffs(string[] staffcodes)
        {
            int i = 0;
            for (; i < staffcodes.Length; i++)
            {
                DeleteStaff(staffcodes[i]);
            }

            return true;
        }

        public bool LockStaff(string staffcode)
        {
            return ExecuteNonQuery(string.Format("UPDATE [Staff] SET Active = 0 WHERE StaffCode = '{0}'", staffcode));
        }

        public bool UnLockStaff(string staffcode)
        {
            return ExecuteNonQuery(string.Format("UPDATE [Staff] SET Active = 1 WHERE StaffCode = '{0}'", staffcode));
        }

        public StaffDao LoginStaff(string staffcode, string password)
        {
            //string strPassEncrypt = Globals.encrypt(pass);
            Hashtable hsObj =
                ExecuteHashtable(string.Format("SELECT * FROM [Staff] WITH (NOLOCK) WHERE StaffCode = '{0}' AND Password = '{1}'",
                    staffcode, password));

            if (hsObj != null)
            {
                return new StaffDao(hsObj);
            }
            return null;
        }

        public string GetStaffName(string staffcode)
        {
            SqlDataReader dr = null;
            string name = string.Empty;
            try
            {
                dr =
                    ExecuteReader(string.Format("SELECT [FullName] FROM [Staff] WITH (NOLOCK) WHERE StaffCode = '{0}'",
                        staffcode));
                while (dr.Read())
                {
                    name = dr[0].ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }

            return name;
        }

        public StaffDao GetStaff(string staffcode)
        {
            Hashtable hsObj = ExecuteHashtable(string.Format("SELECT * FROM [Staff] WITH (NOLOCK) WHERE StaffCode = '{0}'", staffcode));

            if (hsObj != null)
            {
                return new StaffDao(hsObj);
            }

            return null;
        }

        public List<StaffDao> GetStaffSupport()
        {
            List<Hashtable> lsObj = ExecuteArrayHastable("SELECT TOP 3 * FROM [Staff] WITH (NOLOCK) WHERE [Support] = 1");

            var lsStaffs = new List<StaffDao>(lsObj.Count);

            for (int i = 0; i < lsObj.Count; i++)
            {
                var staff = new StaffDao(lsObj[i]);
                lsStaffs.Add(staff);
            }

            return lsStaffs;
        }

        public List<StaffDao> GetAllStaffs()
        {
            List<Hashtable> lsObj = ExecuteArrayHastable("SELECT * FROM [Staff] WITH (NOLOCK)");

            var lsStaffs = new List<StaffDao>(lsObj.Count);

            for (int i = 0; i < lsObj.Count; i++)
            {
                var staff = new StaffDao(lsObj[i]);
                lsStaffs.Add(staff);
            }

            return lsStaffs;
        }

        public List<StaffDao> GetAllStaffsExceptCode(string staffcode)
        {
            List<Hashtable> lsObj = ExecuteArrayHastable(string.Format("SELECT * FROM [Staff] WITH (NOLOCK) WHERE [StaffCode] NOT IN ('{0}')", staffcode));

            var lsStaffs = new List<StaffDao>(lsObj.Count);

            for (int i = 0; i < lsObj.Count; i++)
            {
                var staff = new StaffDao(lsObj[i]);
                lsStaffs.Add(staff);
            }

            return lsStaffs;
        }

        public List<StaffDao> GetAllStaffsExceptManager()
        {
            List<Hashtable> lsObj = ExecuteArrayHastable("SELECT * FROM [Staff] WITH (NOLOCK) WHERE [PTNRight] < 3");

            var lsStaffs = new List<StaffDao>(lsObj.Count);

            for (int i = 0; i < lsObj.Count; i++)
            {
                var staff = new StaffDao(lsObj[i]);
                lsStaffs.Add(staff);
            }

            return lsStaffs;
        }
    }
}
